USE [master]
GO

/****** Object:  Login [BUILTIN\Users]    Script Date: 12/04/2011 20:27:43 ******/
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
/****** Object:  Login [fpAdmin]    Script Date: 12/04/2011 20:27:43 ******/
CREATE LOGIN [fpAdmin] WITH PASSWORD=N'password', DEFAULT_DATABASE=[FootballPoll], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'fpAdmin', @rolename = N'dbcreator'
GO

/*ALTER LOGIN [fpAdmin] DISABLE*/
GO
/****** Object:  Login [NT AUTHORITY\SYSTEM]    Script Date: 12/04/2011 20:27:43 ******/
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
/****** Object:  Login [NT SERVICE\MSSQL$SQLEXPRESS]    Script Date: 12/04/2011 20:27:43 ******/
CREATE LOGIN [NT SERVICE\MSSQL$SQLEXPRESS] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
/****** Object:  Login [Saphire\Greg]    Script Date: 12/04/2011 20:27:43 ******/

/*NOTE  ALTER THIS LOGIN TO BE THE APPROPRIATE NT LOGIN FOR WINDOWS AUTHENTICATION  */
CREATE LOGIN [Saphire\Greg] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO


USE [FootballPoll]
GO




/****** Object:  Table [dbo].[Teams]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teams](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[teamName] [nchar](100) NOT NULL,
	[teamConference] [nchar](100) NOT NULL,
 CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Teams] ON
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (1, N'Washington', N'Pac 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (2, N'Boston College', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (3, N'Clemson', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (4, N'Duke', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (5, N'Florida State', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (6, N'Georgia Tech', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (7, N'Maryland', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (8, N'Miami', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (9, N'NC State', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (10, N'North Carolina', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (11, N'Virginia', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (12, N'Virginia Tech', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (13, N'Wake Forest', N'ACC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (14, N'Baylor', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (15, N'Iowa State', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (16, N'Kansas', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (17, N'Kansas State', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (18, N'Missouri', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (19, N'Oklahoma', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (20, N'Oklahoma State', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (21, N'Texas', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (22, N'Texas A&M', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (23, N'Texas Tech', N'Big 12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (24, N'Cincinnati', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (25, N'Connecticut', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (26, N'Louisville', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (27, N'Pittsburgh', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (28, N'Rutgers', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (29, N'South Florida', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (30, N'Syracuse', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (31, N'West Virginia', N'Big East')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (32, N'Illinois', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (33, N'Indiana', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (34, N'Iowa', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (35, N'Michigan', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (36, N'Michigan State', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (37, N'Minnesota', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (38, N'Nebraska', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (39, N'Northwestern', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (40, N'Ohio State', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (41, N'Penn State', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (42, N'Purdue', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (43, N'Wisconsin', N'Big Ten')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (44, N'ECU', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (45, N'Houston', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (46, N'Marshall', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (47, N'Memphis', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (48, N'Rice', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (49, N'SMU', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (50, N'Southern Miss', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (51, N'Tulane', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (52, N'Tulsa', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (53, N'UAB', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (54, N'UCF', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (55, N'UTEP', N'teamConference USA')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (56, N'Army', N'Independents')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (57, N'Navy', N'Independents')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (58, N'Notre Dame', N'Independents')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (59, N'Akron', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (60, N'Ball State', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (61, N'Bowling Green', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (62, N'Buffalo', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (63, N'Central Michigan', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (64, N'Eastern Michigan', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (65, N'Kent State', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (66, N'Miami University', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (67, N'Northern Illinois', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (68, N'Ohio', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (69, N'Temple', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (70, N'Toledo', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (71, N'Western Michigan', N'Mid-American')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (72, N'Air Force', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (73, N'BYU', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (74, N'Colorado State', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (75, N'New Mexico', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (76, N'San Diego State', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (77, N'TCU', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (78, N'UNLV', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (79, N'Wyoming', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (80, N'Boise State', N'Mountain West')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (81, N'Arizona', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (82, N'Arizona State', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (83, N'California', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (84, N'Colorado', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (85, N'Oregon', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (86, N'Oregon State', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (87, N'Stanford', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (88, N'UCLA', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (89, N'USC', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (90, N'Utah', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (91, N'Washington State', N'Pac-12')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (92, N'Alabama', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (93, N'Arkansas', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (94, N'Auburn', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (95, N'Florida', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (96, N'Georgia', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (97, N'Kentucky', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (98, N'LSU', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (99, N'Mississippi State', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (100, N'Ole Miss', N'SEC')
GO
print'Processed 100 total records'
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (101, N'South Carolina', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (102, N'Tennessee', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (103, N'Vanderbilt', N'SEC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (104, N'Arkansas State', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (105, N'FIU', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (106, N'Florida Atlantic', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (107, N'Louisiana-Lafayette', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (108, N'Louisiana-Monroe', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (109, N'Middle Tennessee', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (110, N'North Texas', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (111, N'Troy', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (112, N'Western Kentucky', N'S. Belt')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (113, N'Fresno State', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (114, N'Hawaii', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (115, N'Idaho', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (116, N'Louisiana Tech', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (117, N'Nevada', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (118, N'New Mexico State', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (119, N'San Jose State', N'WAC')
INSERT [dbo].[Teams] ([id], [teamName], [teamConference]) VALUES (120, N'Utah State', N'WAC')
SET IDENTITY_INSERT [dbo].[Teams] OFF





/****** Object:  Table [dbo].[Roles]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
	[Role] [nchar](100) NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[Role] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Roles] ([Role]) VALUES (N'admin')
INSERT [dbo].[Roles] ([Role]) VALUES (N'guest')
INSERT [dbo].[Roles] ([Role]) VALUES (N'user')





/****** Object:  Table [dbo].[VotingPeriod]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VotingPeriod](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[vPeriodStart] [date] NOT NULL,
	[vPeriodEnd] [date] NOT NULL,
	[vDaysOpen] [nchar](60) NOT NULL,
 CONSTRAINT [PK_VotingPeriod] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[VotingPeriod] ON
INSERT [dbo].[VotingPeriod] ([id], [vPeriodStart], [vPeriodEnd], [vDaysOpen]) VALUES (1, CAST(0xCC340B00 AS Date), CAST(0x3E350B00 AS Date), N'Monday,Tuesday,Wednesday,Thursday,Friday')
SET IDENTITY_INSERT [dbo].[VotingPeriod] OFF





/****** Object:  Table [dbo].[VoteSource]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VoteSource](
	[id] [int] NOT NULL,
	[VoteSource] [nchar](10) NOT NULL,
 CONSTRAINT [PK_Vote_Source] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[VoteSource] ([id], [VoteSource]) VALUES (1, N'gasf.com')





/****** Object:  Table [dbo].[Users]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userName] [nchar](100) NOT NULL,
	[password] [nchar](100) NOT NULL,
	[role] [nchar](100) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([id], [userName], [password], [role]) VALUES (1, N'Admin', N'password', N'admin')
INSERT [dbo].[Users] ([id], [userName], [password], [role]) VALUES (2, N'gonzo', N'biking', N'user')
INSERT [dbo].[Users] ([id], [userName], [password], [role]) VALUES (3, N'daman', N'password', N'guest')
INSERT [dbo].[Users] ([id], [userName], [password], [role]) VALUES (4, N'guest', N'password', N'guest')
SET IDENTITY_INSERT [dbo].[Users] OFF





/****** Object:  Table [dbo].[Votes]    Script Date: 12/04/2011 20:27:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Votes](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[teamID] [int] NOT NULL,
	[Points] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[Date] [datetime] NOT NULL,
	[FirstPlaceVotes] [int] NOT NULL,
	[VoteSourceID] [int] NULL,
 CONSTRAINT [PK_Votes] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO






/****** Object:  StoredProcedure [dbo].[GetMostRecentVoteByUserName]    Script Date: 12/04/2011 20:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMostRecentVoteByUserName] 
    /****** Pass in parameters  ******/
   
		@UserName VARCHAR(255) 
AS 
     /****** Explicit declaration for testing  ******/
    /*
		DECLARE @UserName VARCHAR(255) 
		SET @UserName ='gonzo' 
	*/	
	/****** Explicit declaration for testing  ******/

SELECT v.userid, 
       v.DATE,
       u.userName       
FROM   votes v 
       LEFT JOIN users u 
         ON u.id = v.userid 
            AND u.username = @UserName 
WHERE  v.DATE = (SELECT MAX(DATE) 
                 FROM   votes AS v 
                 WHERE  v.userid = u.id) 
GROUP  BY v.userid, 
          v.DATE,
          u.userName
GO
/****** Object:  StoredProcedure [dbo].[GetCurrentVotesByUserName]    Script Date: 12/04/2011 20:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCurrentVotesByUserName] 
     /****** Pass them in as parameters  ******/
   
		@StartDate DATETIME,
		@EndDate DATETIME,
		@UserName varchar(255)
AS 
     /****** Explicit declaration for testing  ******/
    /*
		DECLARE @StartDate DATETIME
		DECLARE @EndDate DATETIME
		DECLARE @UserName varchar(255)


		SET @StartDate ='2011-10-23'
		SET @EndDate ='2011-10-29'
		SET @UserName ='gonzo'
	*/		
	/****** Explicit declaration for testing  ******/

	

SELECT t.ID, t.TeamName, 
v.Points, v.FirstPlaceVotes, v.Date

FROM Teams t

	LEFT JOIN Votes v
	ON v.TeamID = t.ID		
	AND v.Date BETWEEN @StartDate AND @EndDate	 	
	
	LEFT JOIN Users u
	ON u.ID = v.UserID
	AND u.UserName =@UserName

WHERE v.Points IS NOT NULL
ORDER BY v.Points DESC, t.TeamName
GO





/****** Object:  StoredProcedure [dbo].[GetCurrentVotesByUserID]    Script Date: 12/04/2011 20:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCurrentVotesByUserID] 
    /****** Pass them in as parameters  ******/
   
		@StartDate DATETIME,
		@EndDate DATETIME,
		@UserID Int
AS 
     /****** Explicit declaration for testing  ******/
    /*
		DECLARE @StartDate DATETIME
		DECLARE @EndDate DATETIME
		DECLARE @UserID Int
		SET @StartDate ='2011-10-23'
		SET @EndDate ='2011-10-29'
		SET @UserID = 1
		*/	
/****** Explicit declaration for testing  ******/

	
Select votes.teamID,  teams.teamName, SUM(votes.Points) Points, SUM(votes.FirstPlaceVotes) FP
from votes
Left Outer Join  teams
on votes.teamID = teams.id
Join users
on Votes.UserID = Users.id
Where
		Votes.UserID = @UserID /*here i want to use the username */
and
		(SELECT DATEDIFF(Day, votes.Date , @EndDate)) >= 0
and
		(SELECT DATEDIFF(Day, votes.Date , @StartDate)) <= 0
  GROUP BY teamID, teams.teamName
  
  Order by sum(votes.Points) DESC
GO





/****** Object:  StoredProcedure [dbo].[GetCurrentTeamRankedByPoints]    Script Date: 12/04/2011 20:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCurrentTeamRankedByPoints] 
    /****** Pass them in as parameters  ******/
   
		@StartDate DATETIME,
		@EndDate DATETIME
     
AS 
     /****** Explicit declaration for testing  ******/
    /*
		DECLARE @StartDate DATETIME
		DECLARE @EndDate DATETIME
		SET @StartDate ='2010-04-10'
		SET @EndDate ='2010-04-16'
		*/
Select votes.teamID,  teams.teamName, SUM(votes.Points) Points, SUM(votes.FirstPlaceVotes) FP
from votes
Left Outer Join  teams
on votes.teamID = teams.id
Where
		(SELECT DATEDIFF(Day, votes.Date , @EndDate)) >= 0
and
		(SELECT DATEDIFF(Day, votes.Date , @StartDate)) <= 0
  GROUP BY teamID, teams.teamName
  
  Order by sum(votes.Points) DESC
GO





/****** Object:  StoredProcedure [dbo].[GetAllVoteDatesByUserName]    Script Date: 12/04/2011 20:27:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllVoteDatesByUserName] 
	/****** Pass in parameters  ******/ 
		@UserName VARCHAR(255) 
AS 
	/****** Explicit declaration for testing  ******/ 
	/* 
		DECLARE @UserName VARCHAR(255)  
		SET @UserName ='gonzo'  
	*/ 
	/****** Explicit declaration for testing  ******/ 
  SELECT v.userid, 
         v.DATE, 
         v.votesourceid 
  FROM   votes v 
         LEFT JOIN users u 
           ON u.id = v.userid 
              AND u.username = @UserName 
  WHERE  v.userid = u.id 
  GROUP  BY v.userid, 
            v.DATE, 
            v.votesourceid
GO


/****** Object:  Default [DF_Votes_FirstPlaceVotes]    Script Date: 12/04/2011 20:27:43 ******/
ALTER TABLE [dbo].[Votes] ADD  CONSTRAINT [DF_Votes_FirstPlaceVotes]  DEFAULT ((0)) FOR [FirstPlaceVotes]
GO
/****** Object:  ForeignKey [FK_Users_Role]    Script Date: 12/04/2011 20:27:43 ******/
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Role] FOREIGN KEY([role])
REFERENCES [dbo].[Roles] ([Role])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Role]
GO
/****** Object:  ForeignKey [FK_Votes_Teams]    Script Date: 12/04/2011 20:27:43 ******/
ALTER TABLE [dbo].[Votes]  WITH CHECK ADD  CONSTRAINT [FK_Votes_Teams] FOREIGN KEY([teamID])
REFERENCES [dbo].[Teams] ([id])
GO
ALTER TABLE [dbo].[Votes] CHECK CONSTRAINT [FK_Votes_Teams]
GO
/****** Object:  ForeignKey [FK_Votes_Users]    Script Date: 12/04/2011 20:27:43 ******/
ALTER TABLE [dbo].[Votes]  WITH CHECK ADD  CONSTRAINT [FK_Votes_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([id])
GO
ALTER TABLE [dbo].[Votes] CHECK CONSTRAINT [FK_Votes_Users]
GO
/****** Object:  ForeignKey [FK_Votes_VoteSource]    Script Date: 12/04/2011 20:27:43 ******/
ALTER TABLE [dbo].[Votes]  WITH CHECK ADD  CONSTRAINT [FK_Votes_VoteSource] FOREIGN KEY([VoteSourceID])
REFERENCES [dbo].[VoteSource] ([id])
GO
ALTER TABLE [dbo].[Votes] CHECK CONSTRAINT [FK_Votes_VoteSource]
GO
